package com.xinhe.web.reposity;

import com.xinhe.web.entity.AdoptedTreeOrder;
import com.xinhe.web.request.MemberQuery;
import com.xinhe.web.response.MemberMonthView;
import com.xinhe.web.response.MemberPlatformMothView;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

public interface AdoptedTreeOrderRepository  extends JpaRepository<AdoptedTreeOrder, Long> {
    @Query("from AdoptedTreeOrder where code = ?1")
    AdoptedTreeOrder findByCode(String code);
    @Query(value = "select ifnull(sum(amount),0) from tb_adopted_tree_order where removed = 0  and status ='PAY' ", nativeQuery = true)
    int findByCompanySum();
    @Query(value = "select ifnull(sum(pay_fee),0) from tb_adopted_tree_order where removed=0 and status ='PAY' ", nativeQuery = true)
    BigDecimal findByTotalMoney();
    @Query(value = "select ifnull(sum(amount),0) from tb_adopted_tree_order where removed=0 and status='PAY' and to_days(create_time) = to_days(now())  ", nativeQuery = true)
    Integer findByToDayAdopted();
    @Query(value = "select ifnull(sum(pay_fee),0) from tb_adopted_tree_order where removed=0 and status='PAY' and to_days(create_time) = to_days(now())", nativeQuery = true)
    BigDecimal findByToDayAdoptedMoney();
    @Query(value = "select ifnull(sum(amount),0) from tb_adopted_tree_order where removed=0 and status='PAY' and DATE_FORMAT(create_time, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' ) ", nativeQuery = true)
    Integer findByThisMonthAdopted();
    @Query(value = "select ifnull(sum(pay_fee),0) from tb_adopted_tree_order where removed=0 and status='PAY' and DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' ) ", nativeQuery = true)
    BigDecimal findByThisMonthAdoptedMoney();
    @Query(value = "select ifnull(sum(amount),0) from tb_adopted_tree_order where removed=0 and status='PAY' and  PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( create_time, '%Y%m' ) ) =1", nativeQuery = true)
    Integer findByLastMonthAdopted();
    @Query(value = "select ifnull(sum(pay_fee),0) from tb_adopted_tree_order where removed=0 and status='PAY' and PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( create_time, '%Y%m' ) ) =1", nativeQuery = true)
    BigDecimal findByLastMonthAdoptedMoney();
    @Query(value = "select " +
            "(select ifnull(sum(amount),0) from tb_adopted_tree_order where removed = 0 and status ='PAY' and f_user_id=?1  and PERIOD_DIFF( date_format( now() , '%Y%m' ),date_format(create_time, '%Y%m' )) =1) lastMonth," +
            "(select ifnull(sum(amount),0) from tb_adopted_tree_order where removed = 0 and status ='PAY' and f_user_id=?1 and DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m')) thisMonth," +
            "(select ifnull(sum(amount),0) from tb_adopted_tree_order where removed = 0 and status ='PAY' and f_user_id=?1)sum," +
            "(select ifnull(sum(pay_fee),0) from tb_adopted_tree_order where removed = 0 and status ='PAY' and f_user_id=?1)sumMoney," +
            "(select ifnull(sum(amount),0) from tb_adopted_tree_order where removed = 0 and status ='PAY' and f_user_id=?1  and to_days(create_time)=to_days(now())) toDayAdopted," +
            "(select ifnull(sum(pay_fee),0) from tb_adopted_tree_order where removed = 0 and status ='PAY' and f_user_id=?1  and to_days(create_time)=to_days(now())) toDayAdoptedMoney ," +
            "(select ifnull(sum(pay_fee),0) from tb_adopted_tree_order where removed = 0 and status ='PAY' and f_user_id=?1 and DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m')) thisMonthAdoptedMoney," +
            "(select ifnull(sum(pay_fee),0) from tb_adopted_tree_order where removed = 0 and status ='PAY' and f_user_id=?1  and PERIOD_DIFF( date_format( now() , '%Y%m' ),date_format(create_time, '%Y%m' )) =1) lastAdoptedMoney" +
            "", nativeQuery = true)
    MemberMonthView findByServiceMember(Long fUserId);

    @Query(value = "select count(1) from tb_adopted_tree_order where removed=0 and f_user_id=?1 and status =?2", nativeQuery = true)
    Integer findByCountUserIdAndStatus(Long fUserId, String status);

    @Query(value = "select ifnull(sum(amount),0) from tb_adopted_tree_order where removed=0 and status='PAY' and f_user_id=?1 and to_days(create_time) < to_days(?2)", nativeQuery = true)
    Integer findByUserIdAndBig(Long fUserId, Date date);

    @Query(value = "select ifnull(sum(amount),0) from tb_adopted_tree_order where removed=0 and status='PAY' and f_user_id = ?1", nativeQuery = true)
    Integer findByUserId(Long fUserId);

    @Query(value = "select * from tb_adopted_tree_order where removed = 0 and status = 'UNPAY' and  TIMESTAMPDIFF(MINUTE,create_time,NOW()) > ?1", nativeQuery = true)
    List<AdoptedTreeOrder> findOvertimeOrder(Integer teamTimeoutMinuteStr);
    @Query(value = "select ifnull(sum(amount),0) from tb_adopted_tree_order where removed=0 and status='PAY' and TO_DAYS( NOW( ) ) - TO_DAYS(create_time) = 1", nativeQuery = true)
    Integer findByYesterdayAdopted();
    @Query(value = "select ifnull(sum(pay_fee),0) from tb_adopted_tree_order where removed=0 and status='PAY' and TO_DAYS( NOW( ) ) - TO_DAYS( create_time) = 1", nativeQuery = true)
    BigDecimal findByYesterdayAdoptedMoney();

    @Query(value = "select ifnull(sum(pay_fee),0) from tb_adopted_tree_order where removed=0 and status='PAY' and f_user_id =?1 and to_days(create_time)=to_days(now())", nativeQuery = true)
    BigDecimal findUserIdAndAdoptedTReeMoney1(Long userId);

    @Query(value = "select ifnull(sum(pay_fee),0) from tb_adopted_tree_order where removed=0 and status='PAY' and f_user_id =?1 and TO_DAYS( NOW() ) - TO_DAYS( create_time) = 1", nativeQuery = true)
    BigDecimal findUserIdAndAdoptedTReeMoney2(Long userId);

    @Query(value = "select ifnull(sum(pay_fee),0) from tb_adopted_tree_order where removed=0 and status='PAY' and f_user_id =?1 and PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( create_time, '%Y%m' ) ) =1", nativeQuery = true)
    BigDecimal findUserIdAndAdoptedTReeMoney3(Long userId);

    @Query(value = "select ifnull(sum(pay_fee),0) from tb_adopted_tree_order where removed=0 and status='PAY' and f_user_id =?1 and DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' )", nativeQuery = true)
    BigDecimal findUserIdAndAdoptedTReeMoney4(Long userId);

    @Query(value = "select ifnull(sum(pay_fee),0) from tb_adopted_tree_order where removed=0 and status='PAY' and f_user_id =?1", nativeQuery = true)
    BigDecimal findUserIdAndAdoptedTReeMoney5(Long userId);

    @Query(value = "select DATE_FORMAT(create_time,'%Y-%m-%d') time ,ifnull(sum(amount),0) amount from tb_adopted_tree_order WHERE removed=0  and status='PAY' and DATE(create_time)>=DATE_SUB(CURDATE(), INTERVAL 29 DAY) GROUP BY time", nativeQuery = true)
    List<Map<String,String>> findByMothCount();
    @Query(value = "select DATE_FORMAT(create_time,'%Y-%m-%d') time ,ifnull(sum(pay_fee),0) amount from tb_adopted_tree_order WHERE removed=0 and status='PAY' and DATE(create_time)>=DATE_SUB(CURDATE(), INTERVAL 29 DAY) GROUP BY time", nativeQuery = true)
    List<Map<String,String>> findByOrderPayMothCount();
    @Query(value = "select sum(amount) from tb_adopted_tree_order where removed = 0 and f_user_id = ?1 and status ='PAY'", nativeQuery = true)
    Integer adoptedTreeCount(Long userId);

    @Query(value = "select ifnull(sum(pay_fee),0) from tb_adopted_tree_order where removed=0 and status='PAY' and f_user_id=?1 ", nativeQuery = true)
    BigDecimal findByUserAndAdoptedTreeMoney(Object userId);

    @Query(value = "select ifnull(sum(amount),0) from tb_adopted_tree_order where removed = 0 and f_user_id = ?1 and status = 'PAY' and PERIOD_DIFF( date_format( now( ) , '%Y%m' ) ,date_format( create_time, '%Y%m' ) ) =1  ", nativeQuery = true)
    Integer findByLastMonth(Long fUserId);

    @Query(value = "select ifnull(sum(amount),0) from tb_adopted_tree_order where removed = 0 and f_user_id = ?1 and status = 'PAY' and DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )", nativeQuery = true)
    Integer findByThisMonth(Long fUserId);

    @Query(value = "select ifnull(sum(amount),0) from tb_adopted_tree_order where removed = 0 and f_user_id = ?1 and status = 'PAY'", nativeQuery = true)
    Integer findBySum(Long fUserId);

    @Query(value = "select ifnull(sum(amount),0) from tb_adopted_tree_order where removed=0 and status='PAY' and f_user_id=?1 and to_days(create_time) = to_days(:time)", nativeQuery = true)
    Integer findByTimeAndUserId(String time, Long fUserId);

    @Query(value = "select DATE_FORMAT(create_time,'%Y-%m-%d') days ,sum(amount) num from tb_adopted_tree_order WHERE removed=0 and status='PAY' and  create_time BETWEEN  DATE_SUB(NOW(), INTERVAL 6 DAY) and  date(NOW())+1 GROUP BY days", nativeQuery = true)
    List<Map<String,Object>> findByCompany();
    @Query(value = "select sum(amount) from tb_adopted_tree_order where removed = 0 and status = 'PAY' and PERIOD_DIFF( date_format( now( ) , '%Y%m' ) ,date_format( create_time, '%Y%m' ) ) =1", nativeQuery = true)
    Integer findByCompanyLastMonth();
    @Query(value = "select sum(amount) from tb_adopted_tree_order where removed = 0  and status = 'PAY' and DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )", nativeQuery = true)
    Integer findByCompanyThisMonth();
    @Query(value = "select sum(amount) from tb_adopted_tree_order where removed=0 and f_user_id=?1 and status ='PAY'", nativeQuery = true)
    Integer findTeamSumAndUserIdCount(Long fUserId);
    @Query(value = "select sum(amount) from tb_adopted_tree_order where removed=0 and f_user_id=?1 and status ='PAY' and  DATE_FORMAT(create_time,'%y-%m-%d') BETWEEN DATE_FORMAT(?2,'%y-%m-%d') and DATE_FORMAT(?3,'%y-%m-%d') ", nativeQuery = true)
    BigDecimal findByTeamSumAndUserIdMoney(Long fUserId,String startTime,String enfTime);
}
